﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Excel = Microsoft.Office.Interop.Excel;
using Microsoft.Office.Interop.Excel;
using ExcelAddIn2010.ExcelOp;
using System.IO;
namespace ExcelAddIn2010
{
    public static class WorkbookOp
    {
        public static void DeleteBlankSheets(Excel.Workbook wkbk)
        {
            Excel.Worksheet wkSht;
            gValue.ExcelApp.DisplayAlerts = false;
            for (int idx = wkbk.Worksheets.Count; idx > 0; idx--)
            {
                wkSht = wkbk.Worksheets[idx];
                if (0 == gValue.ExcelApp.WorksheetFunction.CountA(wkSht.UsedRange))
                {
                    if (wkbk.Worksheets.Count > 0)
                    {
                        wkSht.Delete();
                    }
                }
            }
            gValue.ExcelApp.DisplayAlerts = true;
            wkSht = null;
        }
        public static string[] GetSheetsName(Excel.Workbook wkBk)
        {
            string[] ArrNames = new string[wkBk.Worksheets.Count];
            for (int idx = 1; idx <= wkBk.Worksheets.Count; idx++)
            {
                ArrNames[idx-1] = wkBk.Worksheets[idx].Name;
            }
            return ArrNames;
        }
        public static string[] GetSheetsName()
        {
            return GetSheetsName(gValue.ExcelApp.ActiveWorkbook);
        }
        public static bool HasSheet(Excel.Workbook wkBk, string name)
        {
            for (int idx = 1; idx <= wkBk.Worksheets.Count; idx++)
            {
                if (name == wkBk.Worksheets[idx].Name)
                {
                    return true;
                }
            }
            return false;
        }
        public static void CreatWorkShtContent(WKSHT_CONTENT _ContentType)
        {
            Excel.Workbook wkBk = gValue.ExcelApp.ActiveWorkbook;
            wkShtContents(wkBk, _ContentType);
        }
        /// <summary>
        /// 生成目录
        /// </summary>
        /// <param name="_wkBk"></param>
        /// <param name="_ContentType"></param>
        public static void wkShtContents(Workbook _wkBk, WKSHT_CONTENT _ContentType)
        {
            Worksheet wkSht = _wkBk.Worksheets.Add(Before: _wkBk.Worksheets[1]);
            int CurRow = 2;
            string str = "";
            wkSht.Name = wkShtNewName(_wkBk, "目录");
            wkSht.Cells[1, 1] = "目录";
            wkSht.Cells[1, 1].Resize(1, 2).Merge();
            wkSht.Cells[1, 1].Font.Size = 18;
            wkSht.Cells[1, 1].HorizontalAlignment = XlHAlign.xlHAlignCenter;

            wkSht.Cells[2, 1] = "序号";
            wkSht.Cells[2, 2] = "工作表名称";
            wkSht.Cells[2, 2].HorizontalAlignment = XlHAlign.xlHAlignCenter;
            wkSht.Columns[1].HorizontalAlignment = XlHAlign.xlHAlignCenter;

            switch (_ContentType)
            {
                case WKSHT_CONTENT.NORMAL:
                    foreach (Microsoft.Office.Interop.Excel._Worksheet wkShtTmp in _wkBk.Worksheets)
                    {
                        if ((wkShtTmp.Name != wkSht.Name) && (wkShtTmp.Visible == XlSheetVisibility.xlSheetVisible))
                        {
                            CurRow++;
                            wkSht.Cells[CurRow, 1] = CurRow - 2;
                            wkSht.Hyperlinks.Add(wkSht.Cells[CurRow, 2], Address: "", SubAddress: wkShtTmp.Name + "!A1", TextToDisplay: wkShtTmp.Name);
                        }
                    }
                    break;
                case WKSHT_CONTENT.NORMAL_HIDE:
                    foreach (Microsoft.Office.Interop.Excel._Worksheet wkShtTmp in _wkBk.Worksheets)
                    {
                        if (wkShtTmp.Name != wkSht.Name)
                        {
                            CurRow++;
                            wkSht.Cells[CurRow, 1] = CurRow - 2;
                            wkSht.Hyperlinks.Add(wkSht.Cells[CurRow, 2], Address: "", SubAddress: wkShtTmp.Name + "!A1", TextToDisplay: wkShtTmp.Name);
                            //wkShtTmp.Hyperlinks.Add(wkShtTmp.Cells[1, 1], Address: "", SubAddress: wkSht.Name + "!A1", TextToDisplay: str);
                        }
                    }
                    break;
                case WKSHT_CONTENT.BACK:

                    foreach (Microsoft.Office.Interop.Excel._Worksheet wkShtTmp in _wkBk.Worksheets)
                    {

                        if ((wkShtTmp.Name != wkSht.Name) && (wkShtTmp.Visible == XlSheetVisibility.xlSheetVisible))
                        {
                            str = wkShtTmp.Cells[1, 1].Text;
                            str = str.Replace(" ", "");
                            if (0 == str.Length)
                            {
                                str = "目录";
                            }
                            CurRow++;
                            wkSht.Cells[CurRow, 1] = CurRow - 2;
                            wkSht.Hyperlinks.Add(wkSht.Cells[CurRow, 2], Address: "", SubAddress: wkShtTmp.Name + "!A1", TextToDisplay: wkShtTmp.Name);
                            wkShtTmp.Hyperlinks.Add(wkShtTmp.Cells[1, 1], Address: "", SubAddress: wkSht.Name + "!A1", TextToDisplay: str);
                        }
                    }
                    break;
                case WKSHT_CONTENT.BACK_HIDE:
                    foreach (Microsoft.Office.Interop.Excel._Worksheet wkShtTmp in _wkBk.Worksheets)
                    {

                        if (wkShtTmp.Name != wkSht.Name)
                        {
                            str = wkShtTmp.Cells[1, 1].Text;
                            str = str.Replace(" ", "");
                            if (0 == str.Length)
                            {
                                str = "目录";
                            }
                            CurRow++;
                            wkSht.Cells[CurRow, 1] = CurRow - 2;
                            wkSht.Hyperlinks.Add(wkSht.Cells[CurRow, 2], Address: "", SubAddress: wkShtTmp.Name + "!A1", TextToDisplay: wkShtTmp.Name);
                            wkShtTmp.Hyperlinks.Add(wkShtTmp.Cells[1, 1], Address: "", SubAddress: wkSht.Name + "!A1", TextToDisplay: str);
                        }
                    }
                    break;
                default:
                    break;
            }
            wkSht.Columns.AutoFit();

            wkSht.Cells[1, 1].Select();
        }
        /// <summary>
        /// 返回一个工作表名称
        /// </summary>
        /// <param name="wkBk"></param>
        /// <param name="wkShtName"></param>
        /// <returns></returns>
        public static string wkShtNewName(Workbook wkBk, string wkShtName)
        {
            int NameCnt = 0;
            string NewName = wkShtName;
            while (WkshtNameRepeat(wkBk, NewName))
            {
                NameCnt++;
                NewName = wkShtName + Convert.ToString(NameCnt);
            }
            return NewName;
        }
        /// <summary>
        /// 给出工作表名称，判断工作簿中是否有工作表名称与其相同
        /// </summary>
        /// <param name="wkBk"></param>
        /// <param name="wkShtName"></param>
        /// <returns>重复返回TRUE，否则返回FALSE</returns>
        public static bool WkshtNameRepeat(Workbook wkBk, string wkShtName)
        {
            foreach (Worksheet wkSht in wkBk.Worksheets)
            {
                if (wkShtName == wkSht.Name)
                {
                    return true;
                }
            }
            return false;
        }
        /// <summary>
        /// 移除工作簿保护密码
        /// </summary>
        /// <param name="wkBk"></param>
        public static void RemoveWkbkPassword(Workbook wkBk)
        {
            if ((!wkBk.ProtectStructure) && (!wkBk.ProtectWindows))
            {
                return;
            }
            int FixLen = 11;
            Char[] cBase = { 'a', 'b' };
            Char[] cPassword = new Char[FixLen];
            string strPassword = "";
            for (int idx = 0; idx < Math.Pow(2, FixLen); idx++)
            {
                for (int i = cPassword.GetLowerBound(0); i <= cPassword.GetUpperBound(0); i++)
                {
                    cPassword[i] = cBase[(idx >> i) & 1];
                }
                for (int j = 32; j < 127; j++)
                {
                    strPassword = string.Join("", cPassword) + Convert.ToChar(j);
                    try
                    {
                        wkBk.Unprotect(strPassword);
                    }
                    catch (System.Exception ex)
                    {
                        Console.WriteLine(ex.Message);
                    }
                    if ((!wkBk.ProtectStructure) && (!wkBk.ProtectWindows))
                    {
                        return;
                    }
                }
            }
        }

        public static string RemoveVBAPsd(string filePath)
        {
            if (!File.Exists(filePath))
            {
                return "文件不存在";
            }
            if (Funcs.IsFileInUse(filePath))
            {
                return "文件被占用";
            }
            string dstPath = filePath + ".bak";
            if (File.Exists(dstPath) && Funcs.IsFileInUse(dstPath))
            {
                return "备份文件被占用";
            }
            File.Copy(filePath, dstPath, true);
            long nStart = 0, nEnd = 0;
            byte[] bArr = new byte[3];
            using(FileStream Fs = new FileStream(dstPath, FileMode.Open, FileAccess.Read))
            {
                using (BinaryReader br = new BinaryReader(Fs))
                {
                    byte[] bData;
                    string str;

                    for (long i = 0; i < br.BaseStream.Length - 5; i++)
                    {
                        br.BaseStream.Position = i;
                        bData = br.ReadBytes(5);
                        str = "";
                        for (int idx = 0; idx < bData.Length; idx++)
                        {
                            str += ((char)bData[idx]).ToString();
                        }
                        if (str == "CMG=\"")
                        {
                            nStart = i;
                            br.BaseStream.Position = nStart - 2;
                            bArr[0] = br.ReadByte();
                            bArr[1] = br.ReadByte();
                        }
                        if (str == "[Host")
                        {
                            nEnd = i - 2;
                            br.BaseStream.Position = nEnd + 16;
                            bArr[2] = br.ReadByte();
                            break;
                        }
                        Console.WriteLine(str);
                    }
                    br.Close();
                    Fs.Close();
                }
            }
            

            if (nStart == 0)
            {
                File.Delete(dstPath);
                return "文件没有VBA密码";
            }
            using (FileStream Fs = new FileStream(filePath, FileMode.Open, FileAccess.Write))
            {
                using (BinaryWriter bw = new BinaryWriter(Fs))
                {
                    for (long i = nStart; i <= nEnd; i += 2)
                    {
                        bw.BaseStream.Position = i;
                        bw.Write(bArr[0]);
                        bw.Write(bArr[1]);
                    }
                    if ((nEnd - nStart) % 2 != 0)
                    {
                        bw.BaseStream.Position = nEnd - 1;
                        bw.Write(bArr[2]);
                    }
                    bw.Close();
                    Fs.Close();
                }
            }
            return "";
        }
    }
}
